# -*- coding:utf-8 -*-

from flask.ext import restful
from flask import request
import json
from flask.ext.restful import fields,Resource,marshal_with

from flask.ext.restful import reqparse
from util.DBUtilsTools import DBPool,DBSingle
from apiService.errorCode import CustomFlaskErr, ErrorCode

import hashlib
import logging
import argparse
from util.Logger import Logger

logger = Logger('slowLogAgentApi.log',logging.DEBUG,logging.DEBUG)

dbPoolTool = DBPool()
errorCode = ErrorCode()
conn = dbPoolTool.getConnection()

getMysqlIdFields = {
    'id': fields.Integer,
    'slow_query_log': fields.String,
    'slow_query_log_file': fields.String,
}



class SlowLogHandler(restful.Resource):
    def md5string(self, fingerprint):
        m2 = hashlib.md5()
        m2.update(fingerprint)
        return m2.hexdigest()

    def handler_fingerprint(self, conn, fingerprint, instance_id):
        cursor = conn.cursor()
        md5string = self.md5string(fingerprint)
        # 通过md5值查看sql是否在fingerprint表中，如果在 直接写入slowlog表 不存在 先写入 fingerprint表
        cursor.execute("select id,instance_id,md5code from slow_query_fingerprint where instance_id=" + str(
            instance_id) + " and md5code='" + md5string + "';")
        result = cursor.fetchone()
        if result:
            # 已存在 处理数据并返回TRUE
            cursor.execute(
                "update slow_query_fingerprint set last_apper_time=now(),total_number=total_number+1 where id=" + str(
                    result[0]))
            cursor.execute("commit;")
            cursor.close()
        else:
            # 不存在
            cursor.execute(
                "insert slow_query_fingerprint(instance_id,md5code,sql_fingerprint,first_apper_time,last_apper_time,total_number) values(" + str(
                    instance_id) + ",'" + str(md5string) + "','" + str(fingerprint) + "',now(),now(),1)")
            cursor.execute("commit;")
            cursor.close()

    def insertSlowLog(self, conn, event, instance_id):
        fingerprint_md5 = self.md5string(event['fingerprint'])
        cursor = conn.cursor()
        qtime = ''
        if event.has_key('time') :
            qtime = event['time']

        insert_sql = "insert slow_log(instance_id,query_time,last_errno,rows_examined,rows_sent," \
                     "log_timestamp,fingerprint_md5,bytes_sent,lock_time,killed," \
                     "cmd_user,query_sql,cmd_ip,query_date,cmd_schema) values" \
                     "(" + str(instance_id) + ",'" + str(event['query_time']) + "','" + str(
            event['last_errno']) + "','" + str(event['rows_examined']) + "'" \
                                                                         "," + str(event['rows_sent']) + "," + str(
            event['timestamp']) + ",'" + str(fingerprint_md5) + "'," + str(event['bytes_sent']) + "" \
                                                                                                  ",'" + str(
            event['lock_time']) + "'," + str(event['killed']) + ",'" + str(event['user']) + "','" + str(
            event['sql']) + "'" \
                            ",'" + str(event['ip']) + "','" + str(qtime) + "','" + str(
            event['schema']) + "')"
        print  insert_sql
        cursor.execute(insert_sql)
        cursor.execute("commit;")
        cursor.close

    #获取mysql ID slow_query_log slow_query_log_file
    def getMysqlConf(self,args):
        conf = {}
        poolCursor = conn.cursor()
        getMysqlIdSql="select id,mysql_host,mysql_port,manage_user,manage_password from mysql_instances where mysql_host='%s' and mysql_port=%d;" % (args['ip'],args['port'])
        poolCursor.execute(getMysqlIdSql)
        #获取 ID 和 管理账户
        result = poolCursor.fetchone()
        logger.debug(str("获取mysql id 结果集:"+str(result)))
        if result:
            mysqlId = result[0]
            mysqlHost = result[1]
            mysqlPort = result[2]
            manageUser = result[3]
            managePassword = result[4]

            conf['id'] = mysqlId
        else:
            return conf


        connSingle = DBSingle(mysqlHost,int(mysqlPort),manageUser,managePassword)
        singleConn = connSingle.getConnection()
        singleCursor = singleConn.cursor()
        getSlowQueryLogSql = "show variables like 'slow_query_log';"
        singleCursor.execute(getSlowQueryLogSql)
        result = singleCursor.fetchone()
        logger.debug(str("获取mysql slow_query_log 结果集:" + str(result)))
        conf['slow_query_log'] = result[1]

        getSlowQueryLogFileSql = "show variables like 'slow_query_log_file';"
        singleCursor.execute(getSlowQueryLogFileSql)
        result = singleCursor.fetchone()
        logger.debug(str("获取mysql slow_query_log_file 结果集:" + str(result)))
        conf['slow_query_log_file'] = result[1]

        logger.info(str("获取mysql conf 字典:" + str(conf)))

        return conf




    @marshal_with(getMysqlIdFields)
    def get(self, **kwargs):
        parser = reqparse.RequestParser()
        parser.add_argument('ip', type=str, required=True, help='Agent monitor mysql IP is must required')
        parser.add_argument('port', type=int, required=True, help='Agent monitor mysql port is must required')
        args = parser.parse_args()
        logger.info(str("解析请求API的参数:" + str(args)))

        apiData = self.getMysqlConf(args)

        if apiData:
            logger.info(str("slowLogAgentApi返回数据:" + str(apiData)))
            return apiData,200
        else:
            raise CustomFlaskErr(errorCode.MYSQL_NOT_CONF_IN_ZEUS['return_code'], errorCode.MYSQL_NOT_CONF_IN_ZEUS['status_code'])


    def post(self):
        parser = reqparse.RequestParser()
        parser.add_argument('post_data')
        parser.add_argument('instance_id', type=str)
        args = parser.parse_args()

        id = args.get('instance_id')
        data = args.get('post_data')
        dataList = json.loads(data,encoding='utf-8')
        print dataList
        for dict in dataList:
            self.handler_fingerprint(conn, dict['fingerprint'], int(id))
            self.insertSlowLog(conn, dict, id)


if __name__ == '__main__':
    a = SlowLogHandler()
    a.getMysqlConf({'ip':'192.168.1.104','port':3306})